package com.owen.he.jee.utils.excel;

import java.beans.IntrospectionException;
import java.beans.PropertyDescriptor;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.io.FilenameUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.Assert;

import com.owen.he.jee.utils.ExcelRelation;

/**
 * Excel读取工具类，兼容全部版本
 * 
 * @author owen.he
 *
 */
public class ExcelFileUtils {
	public static final Logger logger = LoggerFactory.getLogger(ExcelFileUtils.class);
	public static final String EXTENSION_XLS = "xls";
	public static final String EXTENSION_XLSX = "xlsx";

	public static int TITLE_ROW_NUM = 0;


	/**
	 * 写入Excel文件
	 * 
	 * @param fileName	文件全路径
	 * @param titleRowNum	title所在行
	 * @param dataList	数据集合
	 * @return
	 * @throws IOException
	 * @author owen.he
	 * @throws IntrospectionException 
	 * @throws InvocationTargetException 
	 * @throws IllegalAccessException 
	 */
	public static <E> String writeFile(String fileName, int titleRowNum, List<E> dataList) throws IOException, IllegalAccessException, InvocationTargetException, IntrospectionException{
		Assert.notNull(fileName, "无效文件路径");
		
		File file = new File(FilenameUtils.getFullPathNoEndSeparator(fileName));
		if (!file.exists()) {
			file.mkdirs();
		}
		Workbook wb = getWorkbookByExtension(fileName);
		FileOutputStream fileOut = null;
		if (wb != null) {
			fileOut = new FileOutputStream(fileName);
			Sheet sheet = wb.createSheet();
			// CellStyle cs = wb.createCellStyle();
			Row row = null;
			int rowIndex = titleRowNum;
			if (dataList == null || dataList.isEmpty())
				Assert.notNull(dataList, "无数据");

			// 读取注解类和对应字段
			Map<SimpleExcel, String> annoFieldMap = readAnnoField(dataList.get(0).getClass(), SimpleExcel.class);
			List<SimpleExcel> titleList = new ArrayList<SimpleExcel>(annoFieldMap.keySet());

			// 根据index排序
			Collections.sort(titleList, new Comparator<SimpleExcel>() {
				public int compare(SimpleExcel arg0, SimpleExcel arg1) {
					return arg0.index() == arg1.index() ? 0 : arg0.index() > arg1.index() ? 1 : -1;
				}
			});

			row = sheet.createRow(rowIndex++);
			writeCellTitle(sheet, row, titleList);

			writeRows(dataList, sheet, rowIndex, annoFieldMap, titleList);
			wb.write(fileOut);
		}

		if (fileOut != null) {
			fileOut.flush();  
			fileOut.close();  

			fileOut = null;
		}
		if (wb != null) {
			wb.close();
			wb = null;
		}
		return fileName;
	}

	/**
	 * 读Excel文件
	 * 
	 * @param fileName	完整文件名
	 * @param titleRowNum	title的行号
	 * @param clazz	转换的对象
	 * @return
	 * @author owen.he
	 * @throws IOException 
	 * @throws IntrospectionException 
	 * @throws InvocationTargetException 
	 * @throws IllegalArgumentException 
	 * @throws NoSuchFieldException 
	 * @throws IllegalAccessException 
	 * @throws InstantiationException 
	 * @throws Throwable 
	 */
	public static <E> List<E> readFiles(String fileName, int titleRowNum, Class<?> clazz)
			throws IOException, InstantiationException, IllegalAccessException, NoSuchFieldException,
			IllegalArgumentException, InvocationTargetException, IntrospectionException {
		List<E> returnList = null;
		Workbook wb = readExcelFile(fileName);
		if (wb != null) {
			Sheet sheet = null;
			Row row = null;
			Object[] titles = null, contents = null;
			E e = null;
			int rowIndex, rows;
			returnList = new ArrayList<>();

			Map<String, String> annoFieldMap = readAnnoNameField(clazz);
			for (int k = 0; k < wb.getNumberOfSheets(); k++) {
				sheet = wb.getSheetAt(k);
				if (sheet == null) {
					continue;
				}
				rowIndex = sheet.getFirstRowNum();
				rows = sheet.getPhysicalNumberOfRows();
				logger.debug("Sheet {} name=\"{}\" has {} row(s)", FilenameUtils.getName(fileName), sheet, rows);
				logger.debug("rowIndex={},rows={},titleRowNum={}", rowIndex, rows, titleRowNum);
				for (Iterator<Row> it = sheet.rowIterator(); it.hasNext();) {
					row = it.next();
					if (row == null) {
						break;
					}
					if (rowIndex++ == titleRowNum) {
						titles = readCellTitle(row);
						continue;
					}
					contents = readCellByRow(row);
					if (contents == null || contents.length == 0)
						continue;

					e = assigned(clazz, annoFieldMap, titles, contents);
					returnList.add(e);
				}
			}
			
			if (wb != null) {
				wb.close();
				wb = null;
			}
		}
		return returnList;
	}

	/**
	 * 构建excel文件
	 * 
	 * @param fileName
	 * @return
	 * @author owen.he
	 */
	private static Workbook readExcelFile(String fileName) {
		Workbook wb = null;
		try {
			wb = WorkbookFactory.create(new File(fileName));
		} catch (EncryptedDocumentException | InvalidFormatException | IOException e) {
			e.printStackTrace();
			if (wb != null) {
				try {
					wb.close();
				} catch (IOException e1) {
					e1.printStackTrace();
				}finally {
					wb = null;
				}
			}
		}
		return wb;
	}
	
	/**
	 * 读Excel文件
	 * 
	 * @param file	文件流
	 * @param titleRowNum	title的行号
	 * @param clazz	转换的对象
	 * @return
	 * @author owen.he
	 * @throws IOException 
	 * @throws IntrospectionException 
	 * @throws InvocationTargetException 
	 * @throws IllegalArgumentException 
	 * @throws NoSuchFieldException 
	 * @throws IllegalAccessException 
	 * @throws InstantiationException 
	 * @throws Throwable 
	 */
	public static <E> List<E> readStreamFiles(InputStream file, int titleRowNum, Class<?> clazz)
			throws IOException, InstantiationException, IllegalAccessException, NoSuchFieldException,
			IllegalArgumentException, InvocationTargetException, IntrospectionException {
		List<E> returnList = null;
		Workbook wb = readStreamToExcelFile(file);
		if (wb != null) {
			Sheet sheet = null;
			Row row = null;
			Object[] titles = null, contents = null;
			E e = null;
			int rowIndex, rows;
			returnList = new ArrayList<>();

			Map<String, String> annoFieldMap = readAnnoImportTitleNameField(clazz);
			for (int k = 0; k < wb.getNumberOfSheets(); k++) {
				sheet = wb.getSheetAt(k);
				if (sheet == null) {
					continue;
				}
				rowIndex = sheet.getFirstRowNum();
				rows = sheet.getPhysicalNumberOfRows();
				logger.debug("Sheet {} row(s)", sheet, rows);
				logger.debug("rowIndex={},rows={},titleRowNum={}", rowIndex, rows, titleRowNum);
				for (Iterator<Row> it = sheet.rowIterator(); it.hasNext();) {
					row = it.next();
					if (row == null) {
						break;
					}
					if (rowIndex++ == titleRowNum) {
						titles = readCellTitle(row);
						continue;
					}
					contents = readCellByRow(row);
					if (contents == null || contents.length == 0)
						continue;

					e = assigned(clazz, annoFieldMap, titles, contents);
					returnList.add(e);
				}
			}
			
			if (wb != null) {
				wb.close();
				wb = null;
			}
		}
		return returnList;
	}
	
	/**
	 * 构建excel文件
	 * 
	 * @param fileName
	 * @return
	 * @author owen.he
	 */
	private static Workbook readStreamToExcelFile(InputStream file) {
		Workbook wb = null;
		try {
			wb = WorkbookFactory.create(file);
		} catch (EncryptedDocumentException | InvalidFormatException | IOException e) {
			e.printStackTrace();
			if (wb != null) {
				try {
					wb.close();
				} catch (IOException e1) {
					e1.printStackTrace();
				}finally {
					wb = null;
				}
			}
		}
		return wb;
	}
	
	/**
	 * 写入一行title
	 * 
	 * @param sheet
	 * @param row
	 * @param titleList
	 * @return
	 */
	private static void writeCellTitle(Sheet sheet, Row row, List<SimpleExcel> titleList) {
		Cell cell = null;
		int colNum = 0;
		for (SimpleExcel se : titleList) {
			if (se.width() > 0) {
				sheet.setColumnWidth(colNum, se.width() * 256);
			}
			cell = row.createCell(colNum++);
			cell.setCellValue(se.name());
		}
	}

	/**
	 * 写一行数据
	 * 
	 * @param contentList
	 * @param sheet
	 * @param rowIndex
	 * @param annoFieldMap
	 * @param titleList
	 * @throws IntrospectionException
	 * @throws IllegalAccessException
	 * @throws InvocationTargetException
	 */
	public static <E> void writeRows(List<E> contentList, Sheet sheet, int rowIndex,
			Map<SimpleExcel, String> annoFieldMap, List<SimpleExcel> titleList)
			throws IntrospectionException, IllegalAccessException, InvocationTargetException {
		Row row = null;
		for (E e : contentList) {
			row = sheet.createRow(rowIndex++);
			writeCell(annoFieldMap, titleList, row, e);
		}
	}

	/**
	 * 根据对象写一行数据
	 * 
	 * @param annoFieldMap
	 * @param titleList
	 * @param row
	 * @param e
	 * @throws IntrospectionException
	 * @throws IllegalAccessException
	 * @throws InvocationTargetException
	 * @author owen.he
	 */
	private static <E> void writeCell(Map<SimpleExcel, String> annoFieldMap, List<SimpleExcel> titleList, Row row,
			E e) throws IntrospectionException, IllegalAccessException, InvocationTargetException {
		Cell cell = null;
		PropertyDescriptor pd = null;
		Method method = null;
		short colNum = 0;
		for (SimpleExcel se : titleList) {
			cell = row.createCell(colNum++);
			pd = new PropertyDescriptor(annoFieldMap.get(se), e.getClass());
			method = pd.getReadMethod();// 获得写方法
			setCellContents(cell, method.invoke(e));
		}
	}

	/**
	 * 根据后缀名判断类型
	 * 
	 * @param filename
	 * @return
	 * @author owen.he
	 */
	public static Workbook getWorkbookByExtension(String filename) {
		String extension = FilenameUtils.getExtension(filename);
		Workbook wb = null;
		switch (extension) {
		case EXTENSION_XLS:
			wb = new HSSFWorkbook();
			break;
		case EXTENSION_XLSX:
			wb = new XSSFWorkbook();
			break;
		default:
			break;
		}
		return wb;
	}

	/**
	 * 读取实体类的注解和对应字段
	 * @param clazz	目标实体类
	 * @return
	 * @author owen.he
	 */
	public static Map<String, String> readAnnoNameField(Class<?> clazz) {
		Map<String, String> keyFieldMap = new HashMap<String, String>();
		SimpleExcel simExcel = null;
		for (Field field : clazz.getDeclaredFields()) {
			simExcel = field.getAnnotation(SimpleExcel.class);
			if (simExcel == null) {
				continue;
			}
			keyFieldMap.put(simExcel.name(), field.getName());
		}
		return keyFieldMap;
	}
	
	/**
	 * 导出-读取实体类的注解和对应字段
	 * @param clazz	目标实体类
	 * @return
	 * @author owen.he
	 */
	public static List<ExcelRelation> readAnnoExportTitleNameField(Class<?> clazz) {
		List<ExcelRelation> excelRelationList = new ArrayList<>();
		SimpleExcel simExcel = null;
		for (Field field : clazz.getDeclaredFields()) {
			simExcel = field.getAnnotation(SimpleExcel.class);
			if (simExcel == null) {
				continue;
			}
			if(!simExcel.isExport()) {
				continue;
			}
			ExcelRelation re = new ExcelRelation(simExcel, field.getName());
			excelRelationList.add(re);
		}
		return excelRelationList;
	}
	
	
	/**
	 * 导出-读取实体类的字段排列字符串
	 * @param clazz	目标实体类
	 * @return
	 * @author owen.he
	 */
	public static String readAnnoExportField(Class<?> clazz) {
		SimpleExcel simExcel = null;
		List<String> columns = new ArrayList<>();
		for (Field field : clazz.getDeclaredFields()) {
			simExcel = field.getAnnotation(SimpleExcel.class);
			if (simExcel == null) {
				continue;
			}
			if(!simExcel.isExport()) {
				continue;
			}
			String fieldStr = simExcel.field();
			String fStr = StringUtils.isBlank(fieldStr)?field.getName():fieldStr;
			columns.add(fieldStr);
		}
		
		String col =  Arrays.toString(columns.toArray(new String[columns.size()]));
		return col;
	}

	/**
	 * 导入读取实体类的注解和对应字段
	 * @param clazz	目标实体类
	 * @return
	 * @author owen.he
	 */
	public static Map<String, String> readAnnoImportTitleNameField(Class<?> clazz) {
		Map<String, String> keyFieldMap = new HashMap<String, String>();
		SimpleExcel simExcel = null;
		for (Field field : clazz.getDeclaredFields()) {
			simExcel = field.getAnnotation(SimpleExcel.class);
			if (simExcel == null) {
				continue;
			}
			if(!simExcel.isImport()) {
				continue;
			}
			keyFieldMap.put(simExcel.name(), field.getName());
		}
		return keyFieldMap;
	}


	/**
	 * 读取实体类的注解类和对应字段
	 * @param clazz
	 * @param anno
	 * @return
	 * @author owen.he
	 */
	@SuppressWarnings("unchecked")
	private static <K extends Annotation> Map<K, String> readAnnoField(Class<?> clazz,
			Class<? extends Annotation> anno) {
		Map<K, String> annoFieldMap = new HashMap<K, String>();
		K t = null;
		for (Field field : clazz.getDeclaredFields()) {
			t = (K) field.getAnnotation(anno);
			if (t == null) {
				continue;
			}
			annoFieldMap.put(t, field.getName());
		}
		return annoFieldMap;
	}

	/**
	 * 赋值对象，暂只支持一个参数的set方法
	 * 
	 * @param clazz
	 * @param annoFieldMap	excel名与字段名
	 * @param titles	excel名数组
	 * @param contents	内容数组
	 * @return
	 * @throws IllegalAccessException 
	 * @throws InstantiationException 
	 * @throws IntrospectionException 
	 * @throws NoSuchFieldException 
	 * @throws InvocationTargetException 
	 * @throws IllegalArgumentException 
	 * @author owen.he
	 */
	@SuppressWarnings("unchecked")
	private static <E> E assigned(Class<?> clazz, Map<String, String> annoFieldMap, Object[] titles, Object[] contents)
			throws InstantiationException, IllegalAccessException, IntrospectionException, NoSuchFieldException,
			IllegalArgumentException, InvocationTargetException {
		E bean = null;
		PropertyDescriptor pd = null;
		Method method = null;
		Class<?> c;
		String fieldName = null;

		for (int i = 0; i < contents.length; i++) {
			if (contents[i] != null) {
				bean = (bean == null) 
						? (E) clazz.newInstance() 
						: bean;
				fieldName = annoFieldMap.get(titles[i]);
				logger.debug("title:{},field:{}", titles[i], fieldName);
				pd = new PropertyDescriptor(fieldName, clazz);
				method = pd.getWriteMethod();// 获得写方法
				c = pd.getPropertyType();

				logger.debug("getParameterTypes={},{}", c.getName(), c.getSuperclass());
				method.invoke(bean, String.valueOf(contents[i]));
			}
		}
		return bean;
	}

	/**
	 * 读取行数据，返回对象数组
	 * 
	 * @param row
	 * @return
	 * @author owen.he
	 */
	private static Object[] readCellByRow(Row row) {
		Object[] valueAry = null;
		if (row == null)
			return valueAry;

		Cell cell = null;
		
//		short minColIx = row.getFirstCellNum();
		short maxColIx = row.getLastCellNum();
		valueAry = new Object[maxColIx];
		// row.getPhysicalNumberOfCells() 没用，因为CELL_TYPE_BLANK也算有效
		logger.debug("maxColIx={},PhysicalNumberOfCells={}",maxColIx,row.getPhysicalNumberOfCells());
		boolean isNotEmpty = false;
		for (short colIx = 0; colIx < maxColIx; colIx++) {
			cell = row.getCell(colIx);
			if (cell == null) {
				continue;
			}
			valueAry[colIx] = readCell(cell);

			if (!isNotEmpty && valueAry[colIx] != null && StringUtils.isNotBlank(String.valueOf(valueAry[colIx]))) {
				isNotEmpty = true;
			}
		}
		
		logger.debug("Row {} has {} cell(s)", row.getRowNum(), row.getPhysicalNumberOfCells());
		logger.debug("The cell(s): {}", Arrays.toString(valueAry));
		return isNotEmpty ? valueAry : null;
	}
	
	/**
	 * 读取标题行数据，返回对象数组
	 * 
	 * @param row
	 * @return
	 * @author owen.he
	 */
	private static Object[] readCellTitle(Row row) {
		return readCellByRow(row);
	}

	/**
	 * 根据单元格取得内容
	 * 
	 * @param cell
	 * @return
	 * @author owen.he
	 */
	private static Object readCell(Cell cell) {
		Object content = null;
		BigDecimal decimal = null;
		switch (cell.getCellType()) {
		case Cell.CELL_TYPE_STRING:
			content = cell.getRichStringCellValue().getString();
			break;
		case Cell.CELL_TYPE_NUMERIC:
			if (DateUtil.isCellDateFormatted(cell)) {
				content = cell.getDateCellValue();
			} else {
				decimal = BigDecimal.valueOf(cell.getNumericCellValue());
				if (decimal.toString().indexOf(".") > 0) {
					content = decimal.doubleValue();
				} else {
					content = decimal.intValue();
				}
			}
			break;
		case Cell.CELL_TYPE_BOOLEAN:
			content = cell.getBooleanCellValue();
			break;
		case Cell.CELL_TYPE_FORMULA:
			content = cell.getCellFormula();
			break;
		default:
			System.out.println();
		}
		return content;

	}

	/**
	 * 根据单元格取得内容
	 * 
	 * @param cell
	 * @return
	 * @author owen.he
	 */
	private static void setCellContents(Cell cell, Object obj) {
		BigDecimal decimal = null;
		// TODO:已加入CellType类型，复杂报表，需要扩展方法
		if (obj instanceof Integer || obj instanceof Float || obj instanceof Double || obj instanceof Long
				|| obj instanceof Date) {
			cell.setCellType(Cell.CELL_TYPE_NUMERIC);
			decimal = new BigDecimal(obj.toString());
			if (decimal.toString().indexOf(".") > 0) {
				cell.setCellValue(decimal.doubleValue());
			} else {
				cell.setCellValue(decimal.intValue());
			}
		} else if (obj instanceof Boolean) {
			cell.setCellType(Cell.CELL_TYPE_BOOLEAN);
			cell.setCellValue(Boolean.parseBoolean(obj.toString()));
		} else if (obj instanceof String) {
			cell.setCellType(Cell.CELL_TYPE_STRING);
			cell.setCellValue(obj.toString());
		}
	}
}
